package com.zwq.repository;

import com.zwq.pojo.entity.Machine;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.LocalDateTime;
import java.util.List;

public interface MachineRepository extends JpaRepository<Machine, Long> {

    @Query("SELECT m.type, COUNT(m) FROM Machine m GROUP BY m.type")
    List<Object[]> countByType();

    @Query("SELECT m.status, COUNT(m) FROM Machine m GROUP BY m.status")
    List<Object[]> countByStatus();

    @Query("SELECT DATE(m.registerTime), SUM(m.usageCount) FROM Machine m " +
            "WHERE m.registerTime BETWEEN :start AND :end GROUP BY DATE(m.registerTime)")
    List<Object[]> sumUsageCountByDate(@Param("start") LocalDateTime start, @Param("end") LocalDateTime end);

    @Query("SELECT m.name, COUNT(m) FROM Machine m WHERE m.status = 'FAULT' GROUP BY m.name")
    List<Object[]> countFailuresByName();

    List<Machine> findByOwnerId(Integer ownerId);

    List<Machine> findByNumberContaining(String number);

    List<Machine> findByNameContaining(String name);

    List<Machine> findByType(String type);

    @Query("SELECT m FROM Machine m WHERE " +
            "(:number IS NULL OR m.number LIKE %:number%) AND " +
            "(:name IS NULL OR m.name LIKE %:name%) AND " +
            "(:type IS NULL OR m.type = :type) AND " +
            "(:ownerId IS NULL OR m.ownerId = :ownerId) AND " +
            "(:start IS NULL OR m.registerTime >= :start) AND " +
            "(:end IS NULL OR m.registerTime <= :end)")
    List<Machine> findByFilters(
            @Param("number") String number,
            @Param("name") String name,
            @Param("type") String type,
            @Param("ownerId") Integer ownerId,
            @Param("start") LocalDateTime start,
            @Param("end") LocalDateTime end);
}